﻿using System.Collections;
using System.ComponentModel;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace KPIS.GERP.ESS.DAL
{
    public class D_FimMoneyRequest
    {
        public DataTable ReportMain(int money_req_seq)
        {
         IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);

                string sql = "select j.job_site_name, mr.money_req_date, mr.money_req_name, mr.emp_seq"
                             + ", mr.borrow_start_date, mr.borrow_end_date"
                             + ", case when mr.contact_seq is not null then c.NameTh"
                             + " else concat(np.prefix_name, e.first_name, ' ', e.last_name)"
                             + " end as payee_name"
                             + ", concat(np.prefix_name, e.first_name, ' ', e.last_name) as emp_name, posi.pos_name"
                             + ", pj.bgm_proj_name, mr.ref_no as rq_ref_no, CONVERT(date,getdate()) as print_date"
                             + " from fim_money_requests mr"
                             + " left join (select mrd.money_req_seq"
                             + ", sum(mrd.amount) as amount"
                             + " from fim_money_request_dtls mrd"
                             + " where mrd.record_status not like 'D'"
                             + " group by mrd.money_req_seq"
                             + ") mrd on mr.money_req_seq = mrd.money_req_seq"

                             + " left join hrm_ps_employees e on mr.emp_seq = e.emp_seq"
                    /************* Add on 4/4/2557 **************/
                             + " left join hrm_mp_positions posi on e.pos_seq = posi.pos_seq"
                    /********************************************/
                             + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                             +
                             " left join (select dep.job_site_seq,case when upper_job_site_seq > 2 then (select job_site_name from hrm_mp_job_sites dep2 where dep2.job_site_seq = dep.upper_job_site_seq)+' '+dep.job_site_name else dep.job_site_name end as job_site_name from hrm_mp_job_sites dep) j on e.working_job_site_seq = j.job_site_seq"
                             + " left join IcmMasContact c on mr.contact_seq = c.ConId"
                             + " left join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq"
                             + " left join bgm_project pj on op.bgm_proj_seq = pj.bgm_proj_seq"
                             + " where mr.money_req_seq = @money_req_seq";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportSub(int money_req_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);

                string sql = "select mrd.money_req_dtls_seq"
                             + ", exp.bgm_exp_name as BgmExpName"
                             + ", mrd.remarks as Remarks"
                             + ", mrd.amount as Amount"
                             + ", mrd.amount_vat as AmountVat"
                             + " from fim_money_request_dtls mrd"
                             +
                             " left join bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
                             + " left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
                             + " left join bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq"
                             + " where mrd.record_status not like 'D'"
                             + " and mrd.amount > 0"
                             + " and mrd.money_req_seq = @money_req_seq";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_Contract(int seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT * FROM vwReportContract WHERE money_req_seq = @seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_ContractSub(int seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                //Modified by Raksakaew Feb 26,2014 add mrd.remarks
                strSQL =
                    "SELECT * FROM vwReportContractSub WHERE money_req_seq = @seq AND amount > 0 AND record_status not like @delete_status";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Getcontactseq(int money_req_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                //Modified by Raksakaew Feb 26,2014 add mrd.remarks
                strSQL =
                    "SELECT contact_seq FROM fim_money_requests where money_req_seq = @money_req_seq AND record_status not like @delete_status";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable GetcontactName(int money_req_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                //Modified by Raksakaew Feb 26,2014 add mrd.remarks
                strSQL =
                    "SELECT A.NameTh as 'ConName' FROM IcmMasContact A left join  fim_money_requests B on A.ConId = B.contact_seq where B.money_req_seq = @money_req_seq AND B.record_status not like @delete_status";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public List<M_FimPaymentCheques> GetPaymentCheques(int pay_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            List<M_FimPaymentCheques> result = new List<M_FimPaymentCheques>();

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@pay_seq", pay_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = @"
SELECT c.[cheque_seq] as field_1
      ,c.[cheque_book_seq] as field_2
      ,c.[cheque_no] as field_3
      ,c.[bank_acc_seq] as field_4
      ,c.[pay_date] as field_5

      ,c.[receiver] as field_6
      ,c.[remarks] as field_7
      ,c.[cheque_status] as field_8
      ,c.[record_status] as field_9
      ,c.[created_by] as field_10

      ,c.[created_when] as field_11
      ,c.[updated_by] as field_12
      ,c.[updated_when] as field_13
      ,c.[actual_pay_date] as field_14
      ,c.[cheque_date] as field_15

      ,c.[tran_grp_seq] as field_16
      ,c.[emp_seq] as field_17
      ,c.[contact_seq] as field_18
	  ,pc.[pay_cheque_seq] as field_19
      ,pc.[pay_seq] as field_20

      ,pc.[cheque_seq] as field_21
      ,pc.[amount] as field_22
      ,pc.[record_status] as field_23
      ,pc.[created_by] as field_24
      ,pc.[created_when] as field_25

      ,pc.[updated_by] as field_26
      ,pc.[updated_when] as field_27

  FROM [dbo].[fim_payment_cheques] pc
  INNER JOIN [dbo].[fim_cheques] c on pc.cheque_seq = c.cheque_seq
  where pc.record_status <> @delete_status and c.record_status <> @delete_status
";

                DataTable dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                foreach (DataRow dr in dt.Rows)
                {
                    M_FimPaymentCheques model = new M_FimPaymentCheques();
                    model.FimCheques.cheque_seq = dr["field_1"] as int? ?? 0;
                    model.FimCheques.cheque_book_seq = dr["field_2"] as int?;
                    model.FimCheques.cheque_no = dr["field_3"] as int?;
                    model.FimCheques.bank_acc_seq = dr["field_4"] as int?;
                    model.FimCheques.pay_date = dr["field_5"] as DateTime?;

                    model.FimCheques.receiver = dr["field_6"] as string;
                    model.FimCheques.remarks = dr["field_7"] as string;
                    model.FimCheques.cheque_status = dr["field_8"] as string;
                    model.FimCheques.record_status = dr["field_9"] as string;
                    model.FimCheques.created_by = dr["field_10"] as int?;

                    model.FimCheques.created_when = dr["field_11"] as DateTime?;
                    model.FimCheques.updated_by = dr["field_12"] as int?;
                    model.FimCheques.updated_when = dr["field_13"] as DateTime?;
                    model.FimCheques.actual_pay_date = dr["field_14"] as DateTime?;
                    model.FimCheques.cheque_date = dr["field_15"] as DateTime?;

                    model.FimCheques.tran_grp_seq = dr["field_16"] as int?;
                    model.FimCheques.emp_seq = dr["field_17"] as int?;
                    model.FimCheques.contact_seq = dr["field_18"] as int?;
                    model.pay_cheque_seq = dr["field_19"] as int? ?? 0;
                    model.pay_seq = dr["field_20"] as int? ?? 0;

                    model.cheque_seq = dr["field_21"] as int? ?? 0;
                    model.amount = dr["field_22"] as float? ?? 0;
                    model.record_status = dr["field_23"] as string;
                    model.created_by = dr["field_24"] as int?;
                    model.created_when = dr["field_25"] as DateTime?;

                    model.updated_by = dr["field_26"] as int?;
                    model.updated_when = dr["field_27"] as DateTime?;

                    result.Add(model);
                }
            }
            finally
            {
                dbManager.Dispose();
            }

            return result;
        }

        public void UpdatePaymentCheques(M_FimPaymentCheques model)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@pay_seq", model.pay_seq);
                dbManager.AddParameters(1, "@cheque_seq", model.cheque_seq);
                dbManager.AddParameters(2, "@amount", model.amount);
                dbManager.AddParameters(3, "@record_status", model.record_status);
                dbManager.AddParameters(4, "@updated_by", model.updated_by);

                dbManager.AddParameters(5, "@pay_cheque_seq", model.pay_cheque_seq);

                strSQL = @"
UPDATE [dbo].[fim_payment_cheques]
   SET [pay_seq] = @pay_seq
      ,[cheque_seq] = @cheque_seq
      ,[amount] = @amount
      ,[record_status] = @record_status
      ,[updated_by] = @updated_by
      ,[updated_when] = GETDATE()
 WHERE pay_cheque_seq = @pay_cheque_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateCheques(M_FimCheques model)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@cheque_book_seq", model.cheque_book_seq);
                dbManager.AddParameters(1, "@cheque_no", model.cheque_no);
                dbManager.AddParameters(2, "@bank_acc_seq", model.bank_acc_seq);
                dbManager.AddParameters(3, "@pay_date", model.pay_date);
                dbManager.AddParameters(4, "@receiver", model.receiver);

                dbManager.AddParameters(5, "@remarks", model.remarks);
                dbManager.AddParameters(6, "@cheque_status", model.cheque_status);
                dbManager.AddParameters(7, "@record_status", model.record_status);
                dbManager.AddParameters(8, "@updated_by", model.updated_by);
                dbManager.AddParameters(9, "@actual_pay_date", model.actual_pay_date);

                dbManager.AddParameters(10, "@cheque_date", model.cheque_date);
                dbManager.AddParameters(11, "@tran_grp_seq", model.tran_grp_seq);
                dbManager.AddParameters(12, "@emp_seq", model.emp_seq);
                dbManager.AddParameters(13, "@contact_seq", model.contact_seq);
                dbManager.AddParameters(14, "@cheque_seq", model.cheque_seq);

                strSQL = @"
UPDATE [dbo].[fim_cheques]
   SET [cheque_book_seq] = @cheque_book_seq
      ,[cheque_no] = @cheque_no
      ,[bank_acc_seq] = @bank_acc_seq
      ,[pay_date] = @pay_date
      ,[receiver] = @receiver

      ,[remarks] = @remarks
      ,[cheque_status] = @cheque_status
      ,[record_status] = @record_status 
      ,[updated_by] = @updated_by
      ,[updated_when] = GETDATE()
      ,[actual_pay_date] = @actual_pay_date

      ,[cheque_date] = @cheque_date
      ,[tran_grp_seq] = @tran_grp_seq
      ,[emp_seq] = @emp_seq
      ,[contact_seq] = @contact_seq

 WHERE cheque_seq = @cheque_seq
";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}